﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace ThinkNet.ORM
{
    public class SqlMapping
    {
        #region 辅助方法
        //数据库链接
        public static IDbConnection GetConnection(string ConnectionStringKey)
        {
            var SqlConnStr = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ToString();
            return new SqlConnection(SqlConnStr);
        }
        //获取数据表名称
        public static string GetTableName(string TypeString)
        {
            var arrType = TypeString.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);

            if (arrType.Length > 0)
                return arrType[arrType.Length - 1];
            else
                return null;
        }
        #endregion


        #region 新增
        public static MSqlData SqlServerInsert<T>(T obj, string tableName, bool IsReturnId = false)
        {
            Type t = obj.GetType();

            List<string> listKey = new List<string>();

            List<string> listValue = new List<string>();

            DynamicParameters p = new DynamicParameters();

            PropertyInfo[] Property = t.GetProperties();

            MPrimaryKey primaryKey = SqlMapping.GetPrimaryKey<T>(t, obj);

            foreach (PropertyInfo pi in Property)
            {
                var myVal = GetValue(pi.Name, obj);

                if (myVal != null)
                {
                    if (primaryKey.IsIncrement && pi.Name == primaryKey.KeyName)
                        continue;

                    var paramName = string.Format("@{0}", pi.Name);

                    listKey.Add(pi.Name);

                    listValue.Add(paramName);

                    p.Add(paramName, myVal);
                }
            }
            var sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2});", tableName, string.Join(",", listKey), string.Join(",", listValue));

            Log.Save(sql);

            return new MSqlData() { sql = sql, param = p };
        }
        #endregion

        #region 更改
        //根据主键更新
        public static MSqlData SqlServerUpdate<T>(T obj, string tableName)
        {
            Type t = obj.GetType();

            var listKey = new List<string>();

            var sbWhere = new StringBuilder();

            DynamicParameters p = new DynamicParameters();

            PropertyInfo[] Property = t.GetProperties();

            MPrimaryKey primaryKey = SqlMapping.GetPrimaryKey<T>(t, obj);

            foreach (PropertyInfo pi in Property)
            {
                var myVal = GetValue(pi.Name, obj);

                if (myVal != null)
                {
                    if (pi.Name == primaryKey.KeyName)
                    {
                        sbWhere.AppendFormat("{0}=@{0}", pi.Name);

                        p.Add(string.Format("@{0}", pi.Name), myVal);

                        continue;
                    }
                    var paramName = string.Format("@{0}", pi.Name);

                    var keyValue = string.Format("{0}=@{0}", pi.Name);

                    listKey.Add(keyValue);

                    p.Add(paramName, myVal);
                }
            }
            var sql = string.Format("UPDATE {0} SET {1} WHERE {2};", tableName, string.Join(",", listKey), sbWhere.ToString());

            Log.Save(sql);

            return new MSqlData() { sql = sql, param = p };
        }

        //根据条件更新
        public static MSqlData SqlServerUpdate<T>(T obj, string tableName, string wheres)
        {
            Type t = obj.GetType();

            var listKey = new List<string>();

            DynamicParameters p = new DynamicParameters();

            PropertyInfo[] Property = t.GetProperties();

            MPrimaryKey primaryKey = SqlMapping.GetPrimaryKey<T>(t, obj);

            foreach (PropertyInfo pi in Property)
            {
                var myVal = GetValue(pi.Name, obj);

                if (myVal != null)
                {
                    if (primaryKey.IsIncrement && pi.Name == primaryKey.KeyName)
                        continue;

                    var paramName = string.Format("@{0}", pi.Name);

                    var keyValue = string.Format("{0}=@{0}", pi.Name);

                    listKey.Add(keyValue);

                    p.Add(paramName, myVal);
                }
            }

            var sql = string.Format("UPDATE {0} SET {1} {2};", tableName, string.Join(",", listKey), wheres);

            Log.Save(sql);

            return new MSqlData() { sql = sql, param = p };
        }
        #endregion

        #region 删除
        //根据主键删除
        public static MSqlData SqlServerDelete<T>(T obj, string tableName)
        {
            Type t = obj.GetType();

            StringBuilder sbWhere = new StringBuilder();

            DynamicParameters p = new DynamicParameters();

            PropertyInfo[] Property = t.GetProperties();

            MPrimaryKey primaryKey = SqlMapping.GetPrimaryKey<T>(t, obj);

            foreach (PropertyInfo pi in Property)
            {
                var myVal = GetValue(pi.Name, obj);

                if (myVal != null)
                {
                    if (pi.Name == primaryKey.KeyName)
                    {
                        sbWhere.AppendFormat("{0}=@{0}", pi.Name);

                        p.Add(string.Format("@{0}", pi.Name), myVal);

                        break;
                    }
                }
            }
            var sql = string.Format("DELETE FROM {0} WHERE {1};", tableName, sbWhere.ToString());

            Log.Save(sql);

            return new MSqlData() { sql = sql, param = p };
        }

        //根据条件删除
        public static string SqlServerDelete<T>(T obj, string tableName, string wheres)
        {
            var sql = string.Format("DELETE FROM {0} {1};", tableName, wheres);

            Log.Save(sql);

            return sql;
        }
        #endregion

        #region 实体查询
        public static string SqlServerGet<T>(string tableName, string ID, string showCols = "*", bool nolock = true)
        {
            Type t = typeof(T);

            StringBuilder sbCols = new StringBuilder();

            StringBuilder sbWhere = new StringBuilder();

            MPrimaryKey primaryKey = SqlMapping.GetPrimaryKey<T>(t, Activator.CreateInstance<T>());

            if (showCols == "*")
            {
                PropertyInfo[] Property = t.GetProperties();

                foreach (PropertyInfo pi in Property)
                {
                    sbCols.Append(pi.Name + ",");
                }

                sbCols.Remove(sbCols.Length - 1, 1);
            }
            else
            {
                sbCols.Append(showCols);
            }

            sbWhere.AppendFormat("{0}='{1}'", primaryKey.KeyName, ID);

            var sql = string.Format("SELECT {0} FROM {1} {3} WHERE {2};", sbCols.ToString(), tableName, sbWhere.ToString(), NoLock(nolock));

            Log.Save(sql);

            return sql;
        }
        #endregion

        #region 列表查询
        public static string SqlServerGetList<T>(string tableName, string where, string orderBy, string showCols = "*", bool nolock = true)
        {
            Type t = typeof(T);

            StringBuilder sbCols = new StringBuilder();

            if (showCols == "*")
            {
                PropertyInfo[] Property = t.GetProperties();

                foreach (PropertyInfo pi in Property)
                {
                    sbCols.Append(pi.Name + ",");
                }

                sbCols.Remove(sbCols.Length - 1, 1);
            }
            else
            {
                sbCols.Append(showCols);
            }

            var sql = string.Format("SELECT {0} FROM {1} {4} {2} {3};", sbCols.ToString(), tableName, where, orderBy, NoLock(nolock));

            Log.Save(sql);

            return sql;
        }
        #endregion

        #region 分页查询
        public static string SqlServerGetList<T>(string tableName, string where, string orderBy, int pageIndex, out string sqlCount, int pageSize, string showCols = "*", bool nolock = true, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            Type t = typeof(T);

            StringBuilder sbSql = new StringBuilder();

            StringBuilder sbCount = new StringBuilder();

            StringBuilder sbCols = new StringBuilder();

            if (showCols == "*")
            {
                PropertyInfo[] Property = t.GetProperties();

                foreach (PropertyInfo pi in Property)
                {
                    sbCols.Append(pi.Name + ",");
                }

                sbCols.Remove(sbCols.Length - 1, 1);
            }
            else
            {
                sbCols.Append(showCols);
            }

            sbCount.AppendFormat("SELECT Count(1) FROM {0} {2} {1};", tableName, where, NoLock(nolock));

            sqlCount = sbCount.ToString();

            sbSql.AppendFormat("SELECT {0} FROM {1} {3} {2}", sbCols.ToString(), tableName, where, NoLock(nolock));

            string sql = string.Empty;

            if (sqlVersion == ESqlVersion.SqlServer2012)
            {
                sql = GetSqlServerPagedSqlWith2012(sbSql.ToString(), pageIndex, pageSize, orderBy);
            }
            else
            {
                sql = GetSqlServerPagedSqlWith2005(sbSql.ToString(), pageIndex, pageSize, orderBy);
            }

            Log.Save(sql);

            return sql;
        }
        #endregion

        #region Sql分页查询
        public static string ExecuteSqlPage(string selectSql, string orderBy, int pageIndex, out string sqlCount, int pageSize, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            StringBuilder sbCount = new StringBuilder();

            sbCount.AppendFormat("SELECT COUNT(1) FROM ({0}) AS t;", selectSql);

            sqlCount = sbCount.ToString();

            string sqlReurn = string.Empty;

            if (sqlVersion == ESqlVersion.SqlServer2012)
            {
                sqlReurn = GetSqlServerPagedSqlWith2012(selectSql, pageIndex, pageSize, orderBy);
            }
            else
            {
                sqlReurn = GetSqlServerPagedSqlWith2005(selectSql, pageIndex, pageSize, orderBy);
            }

            Log.Save(sqlReurn);

            return sqlReurn;
        }
        #endregion

        #region 分页方法
        public static string GetSqlServerPagedSqlWith2005(string selectSql, int pageIndex, int pageSize, string orderBySql = null)
        {
            string select = "SELECT";

            int sIndex = selectSql.IndexOf(select, 0, StringComparison.CurrentCultureIgnoreCase);

            if (sIndex != -1)
                selectSql = selectSql.Substring(sIndex + select.Length);

            int start = pageSize * (pageIndex - 1) + 1;

            int end = pageSize * (pageIndex - 1) + pageSize;

            if (string.IsNullOrEmpty(orderBySql))
                orderBySql = "ORDER BY (SELECT 0)";
            string strSql = string.Format("WITH t AS ( SELECT ROW_NUMBER() OVER({0}) AS rowNum, {1} ) SELECT * FROM t WHERE rowNum BETWEEN {2} AND {3} ", orderBySql, selectSql, start, end);

            return strSql;
        }

        public static string GetSqlServerPagedSqlWith2012(string selectSql, int pageIndex, int pageSize, string orderBySql = null)
        {
            string select = "SELECT";

            int sIndex = selectSql.IndexOf(select, 0, StringComparison.CurrentCultureIgnoreCase);

            if (sIndex != -1)
                selectSql = selectSql.Substring(sIndex + select.Length);

            int start = pageSize * (pageIndex - 1);

            if (string.IsNullOrEmpty(orderBySql))
                orderBySql = "(SELECT 0)";

            string strSql = string.Format("SELECT {1} {0} OFFSET {2} ROWS FETCH NEXT {3} ROWS ONLY ", orderBySql, selectSql, start, pageSize);

            return strSql;
        }
        #endregion



        #region 是否锁表
        private static string NoLock(bool noLock)
        {
            if (noLock)
                return " (NOLOCK) ";
            else
                return string.Empty;
        }
        #endregion

        #region 自定义参数转换Dappper参数
        public static object XParameters(object param)
        {
            if (param is Dictionary<string, object>)
            {
                return ToDynamicParameters((Dictionary<string, object>)param);
            }
            return param;
        }
        public static DynamicParameters ToDynamicParameters(Dictionary<string, object> dic)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            foreach (KeyValuePair<string, object> kvp in dic)
            {
                dynamicParameters.Add(kvp.Key, kvp.Value);
            }
            return dynamicParameters;
        }
        #endregion

        #region 获取实体中的主键
        public static MPrimaryKey GetPrimaryKey<T>() where T : new()
        {
            T obj = Activator.CreateInstance<T>();
            return SqlMapping.GetPrimaryKey<T>(obj.GetType(), obj);
        }

        public static MPrimaryKey GetPrimaryKey<T>(Type type, T obj)
        {
            return (MPrimaryKey)type.GetMethod("GetKeyName").Invoke(obj, null);
        }
        #endregion

        #region 根据实体名称获取值
        private static string GetValue(string FieldName, object obj)
        {
            object value = obj.GetType().GetProperty(FieldName).GetValue(obj, null);
            if (value == null)
            {
                return null;
            }
            return Convert.ToString(value);
        }
        #endregion
    }
}
